package kr.go.nih.biomedi.pSEVA.bean;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class VariantAnnotation {
	private static VariantAnnotation instance = new VariantAnnotation();
	private String realFolder;
	private String inputFile;
	private List<NovelSNP132> snpList;
	private List<SNP132> resultSNP = new LinkedList<SNP132>();

	public void setRealFolder(String realFolder) {
		this.realFolder = realFolder;
	}
	
	public void setInputFile(String inputFile) {
		this.inputFile = inputFile;
	}
	
	private static VariantAnnotation getInstance() {
		if (instance == null) {
			return new VariantAnnotation();
		} else {
			return instance;
		}
	}
	
	public String getFileFullName() {
		return this.realFolder + this.inputFile + ".sorted.bam.pileup.snp"; 
	}
	
	public List<NovelSNP132> getNovelSNPList() {
		return this.snpList;
	}
	
	public List<SNP132> getSNPList() {
		return this.resultSNP;
	}
	
	private Connection getConnection() throws ClassNotFoundException, SQLException {
		String url = "jdbc:mysql://localhost/pSEVA";
		String user = "root";
		String password = "tlsehddl1";
		
		Class.forName("com.mysql.jdbc.Driver");
		Connection c = DriverManager.getConnection(url, user, password);
		
		return c;
	}
	
	public String doExecute() throws ClassNotFoundException, SQLException {
		// String snpFileName = getFileFullName();
		String snpFileName = "/home/chris/AMC01.hg19.PE.r0.g3.sorted.bam.pileup.snp.10";
		snpList = new LinkedList<NovelSNP132>();
		try {
			BufferedReader in = new BufferedReader(new FileReader(snpFileName));
			String s;

			while ((s = in.readLine()) != null) {
				String[] tmpResult = s.split("\t");
				NovelSNP132 snp = new NovelSNP132();
				snp.setChrom(tmpResult[0]);
				snp.setChromEnd(Integer.parseInt(tmpResult[1]));
				snp.setrBase(tmpResult[2]);
				snp.setcBase(tmpResult[3]);
				snp.setcQual(Integer.parseInt(tmpResult[4]));
				snp.setSnpQual(Integer.parseInt(tmpResult[5]));
				snp.setMaxMQual(Integer.parseInt(tmpResult[6]));
				snp.setDepth(Integer.parseInt(tmpResult[7]));
				snp.setSeq(tmpResult[8]);
				snp.setQual(tmpResult[9]);
				
				if (!searchSNP132(snp)) {
					insetNovelSNP(snp);
				}  else {
					getSNP132(snp);
				}
				snpList.add(snp);
			}
			in.close();
		} catch (IOException e) {
			System.err.println(e);
			System.exit(1);
		}
		
		return "success";
	}
	
	private void insetNovelSNP(NovelSNP132 snp) throws ClassNotFoundException, SQLException {
		Connection conn = getConnection();
		PreparedStatement ps = conn.prepareStatement("insert into novelSNP value (?,?,?,?,?,?,?,?,?,?)");
		ps.setString(1, snp.getChrom());
		ps.setInt(2, snp.getChromEnd());
		ps.setString(3, snp.getrBase());
		ps.setString(4, snp.getcBase());
		ps.setInt(5, snp.getcQual());
		ps.setInt(6, snp.getSnpQual());
		ps.setInt(7, snp.getMaxMQual());
		ps.setInt(8, snp.getDepth());
		ps.setString(9, snp.getSeq());
		ps.setString(10, snp.getQual());

		ps.executeUpdate();
		
		ps.close();
		conn.close();
	}
	
	private List<SNP132> getSNP132(NovelSNP132 snp) throws ClassNotFoundException, SQLException {
			
			
			Connection conn = getConnection();

			PreparedStatement ps = conn.prepareStatement("select * from snp132 where chrom=? and chromEnd=?;");
			ps.setString(1, snp.getChrom());
			ps.setInt(2, snp.getChromEnd());
			
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getString("chrom"));
				SNP132 tmpSNP = new SNP132();
				tmpSNP.setChrom(rs.getString("chrom"));
				tmpSNP.setChromStart(rs.getInt("chromStart"));
				tmpSNP.setChromEnd(rs.getInt("chromEnd"));
				tmpSNP.setName(rs.getString("name"));
				tmpSNP.setObserved(rs.getString("observed"));
				tmpSNP.setMoltype(rs.getString("molType"));
				tmpSNP.setClasstype(rs.getString("class"));
				tmpSNP.setValid(rs.getString("valid"));
				resultSNP.add(tmpSNP);
			}
			
			rs.close();
			ps.close();
			conn.close();
			
		return resultSNP;
	}

	private boolean searchSNP132(NovelSNP132 snp) throws ClassNotFoundException, SQLException {
		Connection conn = getConnection();

		PreparedStatement ps = conn.prepareStatement("select * from snp132 where chrom=? and chromEnd=?;");
		ps.setString(1, snp.getChrom());
		ps.setInt(2, snp.getChromEnd());
		
		ResultSet rs = ps.executeQuery();
		
		if (rs.next()) {
			return true;
		}

		rs.close();
		ps.close();
		conn.close();
		
		return false;
	}
}
